create database PLMSDB
go
use PLMSDB
use master

use master
go
drop database PLMSDB



--Create table Staff--

create table StaffDetail(
	StaffID int identity(1,1),
	FName varchar(50) not null,
	LName varchar(50) not null,
	DOJ datetime,
	Dept varchar(50) not null,
	[Address] varchar(50) not null,
	P_No int not null,
	Gender char(10) not null,
	DOB datetime,
	Edu_qual varchar(50) not null,
	Tech_skills varchar(50) not null,
	Username varchar(100) not null,
	[PassWord] varchar(100) not null,
	[Level] int,
	[Status] bit,
	constraint PK_Staff primary key (StaffID)
)


--Create table Patient Detail--
go
create table PatientDetail(
	PatientID int identity(1,1),
	FName varchar(50) not null,
	LName varchar(50) not null,
	DOT datetime,
	Dept varchar(50) not null,
	[Address] varchar(50) not null,
	PhoneNo int not null,
	Gender char(10) not null,
	DOB datetime,
	Test varchar(50) not null,
	Bill int not null,
	Username varchar(100) not null,
	[Password] varchar(100) not null,
	[Level] int,
    [Status] bit,
     StaffID int references StaffDetail(StaffID) not null ,
	constraint PK_Patient primary key (PatientID)
)



--Create table Billing--
go
create table Billing(
	BillNo int identity(1,1) not null,
	PatientID int references PatientDetail(PatientID) not null,
	Amount int not null,
	constraint PK_Billing primary key (BillNo)
)

--Create table Test--
go
create table Test(
	TestID int identity(1,1),
	TName varchar(50) not null,
	Cost int not null,
	constraint PK_Test primary key (TestID)
)



--Create table StockDetail--
go
create table StockDetail(
	ItemCode int identity(1,1),
	IName varchar(50) not null,
	ICost int not null,
	INum int not null,
	DNum int not null,
		constraint PK_Stock primary key (ItemCode)
		
)

insert into  StockDetail values('HoangDm',1,100,2)

--Create table Test Stock Detail--
go
create table TestStock(
TestID int references Test(TestID) not null,
ItemCode int references StockDetail(ItemCode) not null,
)



--Create table DoctorDetails--
go
create table DoctorDetail(
DoctorID int identity(1,1),
	D_Name varchar(50) not null,
	[Address] varchar(50) not null,
	PhoneNo int not null,
	Specialization varchar(50) not null,
	constraint PK_Doctor primary key (DoctorID)
)

--Create table Report--
go
create table Report(
	PatientID int references PatientDetail(PatientID) not null,
	DoctorID int references DoctorDetail(DoctorID) not null,
	Report varchar(500) not null,
	TestID int references Test(TestID) not null
)

